IF OBJECT_ID(N'[dbo].[usp_GetMaBNMax]') IS NOT NULL
	DROP PROCEDURE [dbo].[usp_GetMaBNMax]
GO

CREATE PROCEDURE [dbo].[usp_GetMaBNMax]	
AS

SET NOCOUNT ON

SELECT MAX(Ma) as Max
FROM BenhNhan

GO

------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[usp_SelectSelectAllDotDieuTriByMaBenhNhan]') IS NOT NULL
	DROP PROCEDURE [dbo].[usp_SelectSelectAllDotDieuTriByMaBenhNhan]
GO

CREATE PROCEDURE [dbo].[usp_SelectSelectAllDotDieuTriByMaBenhNhan]
	@Ma int
AS

SET NOCOUNT ON

SELECT
	Ma,
	NgayKham
FROM
	dbo.DotDieuTri 
WHERE
	MaBenhNhan=@Ma
GO

------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[usp_SelectChiTietDieuTriByBenhNhan]') IS NOT NULL
	DROP PROCEDURE [dbo].[usp_SelectChiTietDieuTriByDotDieuTri]
GO

CREATE PROCEDURE [dbo].[usp_SelectChiTietDieuTriByDotDieuTri]	
	@MaDDT int
AS

SET NOCOUNT ON

SELECT
	ct.Ma,
	ct.MaDichVu,
	ct.MaDotDieuTri,
	ct.Ngay,
	ct.Rang,
	ct.CongViecDieuTri,
	ct.GhiChu
FROM
	dbo.ChiTietDieuTri as ct
	
WHERE
	ct.Ma=@MaDDT
GO

------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[usp_SelectBenhNhanByHoTen]') IS NOT NULL
	DROP PROCEDURE [dbo].[usp_SelectBenhNhanByHoTen]
GO

CREATE PROCEDURE [dbo].[usp_SelectBenhNhanByHoTen]	
	@hoten nvarchar(50)
AS

SET NOCOUNT ON

SELECT*
FROM
	dbo.BenhNhan
WHERE
	HoTen like @hoten
GO

------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[usp_SelectBenhNhanByThongTin]') IS NOT NULL
	DROP PROCEDURE [dbo].[usp_SelectBenhNhanByThongTin]
GO

CREATE PROCEDURE [dbo].[usp_SelectBenhNhanByThongTin]	
	@ma int,
	@hoten nvarchar(50),
	@gioitinh int,
	@ngaysinh datetime
AS

SET NOCOUNT ON

SELECT*
FROM
	dbo.BenhNhan
WHERE
	HoTen like @hoten AND Ma = @ma AND GioiTinh = @gioitinh AND NgaySinh = @ngaysinh
GO

------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'[dbo].[usp_SelectAllChiTietDieuTriByBenhNhan]') IS NOT NULL
	DROP PROCEDURE [dbo].[usp_SelectAllChiTietDieuTriByBenhNhan]
GO

CREATE PROCEDURE [dbo].[usp_SelectAllChiTietDieuTriByBenhNhan]	
	@mabn int	
AS

SET NOCOUNT ON

SELECT*
FROM
	dbo.DotDieuTri as ddt,
	dbo.ChiTietDieuTri as ctdt
WHERE
	ddt.MaBenhNhan = @mabn AND ddt.Ma = ctdt.MaDotDieuTri
GO